1% completed
Problem Statement
Table: TaskLog
Each row in this table logs a task completion by an assignee, indicating the task's ID, the employee who assigned the task, the assignee, and the completion date.
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| task_id | int |
| employee_id | int |
| assignee_id | int |
| completion_date| date |
+----------------+---------+
This table does not have a primary key and may contain duplicate rows.
task_id is the unique identifier for each task.
employee_id and assignee_id may have the same value, indicating the same person.
Develop a solution to find the IDs of employees who have completed at least one task assigned to themselves. The output should be sorted by employee_id in ascending order.
Example
Input:
TaskLog table: +---------+-------------+------------+----------------+ | task_id | employee_id | assignee_id| completion_date| +---------+-------------+------------+----------------+ | 101 | 10 | 15 | 2022-01-01 | | 102 | 11 | 16 | 2022-01-02 | | 103 | 12 | 12 | 2022-01-02 | | 104 | 13 | 13 | 2022-01-03 | | 105 | 14 | 18 | 2022-01-04 | | 106 | 15 | 15 | 2022-01-05 | | 107 | 16 | 10 | 2022-01-05 | +---------+-------------+------------+----------------+
Output:
+-------------+ | employee_id | +-------------+ | 12 | | 13 | | 15 | +-------------+
Employees with IDs 12, 13, and 15 have completed tasks assigned by themselves.
Try It Yourself
TaskLog =
Solution
To identify employees who have completed their own tasks, we need to examine records in the TaskLog table where the employee_id matches the assignee_id.
- Select Employee IDs: Begin with a
SELECTstatement to fetch all employees from theTaskLogtable. - Apply Conditions: Use a
WHEREclause to filter employees who have completed tasks assigned by themselves. - Sort the Results: Finally sort the results
SQL Query
The following SQL query accomplishes this:
SELECT DISTINCT employee_id FROM TaskLog WHERE employee_id = assignee_id ORDER BY employee_id ASC
Step by Step Approach
Step 1: Select Employee IDs
Begin by selecting distinct employee_id from the TaskLog table to ensure each employee is listed only once in the result.
SELECT DISTINCT employee_id FROM TaskLog
Output After Step 1:
+-------------+ | employee_id | +-------------+ | 10 | | 11 | | 12 | | 13 | | 14 | | 15 | | 16 | +-------------+
Step 2: Apply Conditions
Refine the selection to include only those records where the employee_id matches the assignee_id, indicating tasks completed by the assigners themselves.
SELECT DISTINCT employee_id FROM TaskLog WHERE employee_id = assignee_id
Output After Step 2:
+-------------+ | employee_id | +-------------+ | 12 | | 13 | | 15 | +-------------+
Step 3: Sort the Results
Finally, sort the resulting employee IDs in ascending order to comply with the problem statement's requirements.
SELECT DISTINCT employee_id FROM TaskLog WHERE employee_id = assignee_id ORDER BY employee_id ASC
Final Output:
+-------------+ | employee_id | +-------------+ | 12 | | 13 | | 15 | +-------------+
On this page
Problem Statement
Example
Try It Yourself
Solution
SQL Query
Step by Step Approach